<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
  <meta name="keywords" content="hexo,个人博客,blog" />
  <meta name="description" content="赚钱，裸辞开店！！" />
  <meta http-equiv="x-dns-prefetch-control" content="on">
  <link rel="dns-prefetch" href="https://busuanzi.ibruce.info">
  <link rel="dns-prefetch" href="https://at.alicdn.com">
  
  <link rel="dns-prefetch" href="https://widget.daovoice.io">
  <link rel="dns-prefetch" href="https://widget-static-cdn.daovoice.io">
  <link rel="dns-prefetch" href="https://im.daovoice.io">
  
  
  <link rel="dns-prefetch" href="https://hm.baidu.com/">
  
  
  <link rel="stylesheet" type="text/css" href="/./style/main.css">
	<link rel="shortcut icon" href="/favicon.ico" title="Favicon">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.css">
	<title>oracle 多行转多列</title>
  
  <script>var _hmt=_hmt||[];(function(){var hm=document.createElement("script");hm.src="https://hm.baidu.com/hm.js?awwssw1snsnsnn1ndndnndnd99j";var s=document.getElementsByTagName("script")[0];s.parentNode.insertBefore(hm,s);})();
  </script>
  
  
    <script>(function(i,s,o,g,r,a,m){i["DaoVoiceObject"]=r;i[r]=i[r]||function(){(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;a.charset="utf-8";m.parentNode.insertBefore(a,m)})(window,document,"script",('https:' == document.location.protocol ? 'https:' : 'http:') + "//widget.daovoice.io/widget/123456.js","daovoice");daovoice('init',{app_id: "123456"});daovoice('update');
  </script>
  
<meta name="generator" content="Hexo 6.3.0"></head>
<body>
  <canvas id="pattern-placeholder" height="230"></canvas>
<div class="navbar-header">
  <a class="blog-title" href="/">Kyist</a>
  <a class="face-img" href="/">
    <img src="/img/zz.png">
  </a>
</div>
<main>
  <div class="article-title">
    
  
  <h1 class="title">
    oracle 多行转多列
  </h1>
  


    <ul class="article-info">
      <li>
        发布
        <time datetime="2023-01-12T15:58:59.000Z" itemprop="datePublished">2023-01-12</time>
      </li>
      <li>
        
    更新 <time datetime="2023-02-12T16:01:37.012Z" itemprop="dateUpdated">2023-02-13</time>

      </li>
      <li id="busuanzi_container_page_pv">
        阅读 <span id="busuanzi_value_page_pv"></span>
      </li>
    </ul>
  </div>
  <div class="container">
    <div class="article">
      <div class="content">
        
        <pre><code class="sql">-- oracle 多行转多列（数据量大不建议使用）
SELECT JQBH,
    listagg(QRAB) within group( order by JQBH) QRAB,
    listagg(ZDGZ) within group( order by JQBH) ZDGZ,
    listagg(JZLRZL) within group( order by JQBH) JZLRZL,
    listagg(JQTYPE) within group( order by JQBH) JQTYPE,
    listagg(ZARS) within group( order by JQBH) ZARS,
    listagg(CASEPLACE) within group( order by JQBH) CASEPLACE,
    listagg(LATITUDE) within group( order by JQBH) LATITUDE,
    listagg(LONGITUDE) within group( order by JQBH) LONGITUDE,
    listagg(LOSEPROPERTY) within group( order by JQBH) LOSEPROPERTY,
    listagg(CASEMETHOD) within group( order by JQBH) CASEMETHOD,
    listagg(DPLY) within group( order by JQBH) DPLY,
    listagg(LOSEMONEY) within group( order by JQBH) LOSEMONEY,
    listagg(ZACL) within group( order by JQBH) ZACL
from(
    SELECT * from(
    SELECT JQBH,ENAME,DETAIL,ROW_NUMBER() over(PARTITION by ENAME ORDER BY JQBH) from V_JQSTANDARDTABLE
    where JQBH in(&#39;J401002021042019044100806&#39;,&#39;J440115230000201812000056&#39;,&#39;4401002021041919043301046&#39;)
    and ENAME in (&#39;QRAB&#39;, &#39;ZDGZ&#39;, &#39;JZLRZL&#39;, &#39;JQTYPE&#39;, &#39;ZARS&#39;, &#39;CASEPLACE &#39;, &#39;LATITUDE&#39;, &#39;LONGITUDE&#39;, &#39;LOSEPROPERTY&#39; ,&#39;CASEMETHOD&#39; , &#39;DPLY&#39; , &#39;LOSEMONEY&#39; , &#39;ZACL&#39;)
    )a
    pivot(
    MAX(DETAIL) for ENAME in (&#39;QRAB&#39; QRAB, &#39;ZDGZ&#39; ZDGZ, &#39;JZLRZL&#39; JZLRZL, &#39;JQTYPE&#39; JQTYPE,&#39;ZARS&#39;ZARS,
    &#39;CASEPLACE&#39; CASEPLACE, &#39;LATITUDE&#39; LATITUDE, &#39;LONGITUDE&#39; LONGITUDE, &#39;LOSEPROPERTY&#39; LOSEPROPERTY, &#39;CASEMETHOD&#39; CASEMETHOD,
    &#39;DPLY&#39; DPLY,&#39;LOSEMONEY&#39; LOSEMONEY, &#39;ZACL&#39; ZACL)
    )b
)GROUP BY JQBH
</code></pre>

      </div>
        <div class="support-author">
          <p>感谢您的阅读。 🙏
          <a href="" target="_blank">关于转载请看这里</a>
            <!--<a class="btn-pay"  href="#pay-modal">¥ 打赏支持</a>-->
          </p>
        </div>
        <!--
            <div class="like ">
              <div class="like-button">
                <a id="like-note" href="">
                  <i class="icon-heart"></i>喜欢
                </a>
              </div>
              <span id="likes-count">256</span>
            </div>
        -->
        <div class="otherLink">
          <div class="previous">
          </div>
          <div class="next">
          </div>
        </div>
        <div class="comments" id="comments">
          

        </div>
      </div>
    </div>
   </div>
</main>
<div class="footer">
  <div class="info">
    <p>
    <a target="_blank" rel="noopener" href="https://hexo.io"> Hexo </a> 强力驱动 |
      <a target="_blank" rel="noopener" href="https://github.com/Youthink/hexo-themes-yearn"> Yearn </a>
      主题
    </p>
    <p>&copy;2013-2018 某某的博客 京ICP备xxxxxx号</p>
  </div>
</div>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>

<script>//console
  var consoleConfig = ''.split(',');
  var canConsole = ;
  var consoleInfo = (function(consoleConfig) {
  if (!canConsole || !consoleConfig || consoleConfig.length < 1) {
    return;
  }
  var consoleColor = '#6190e8';
  var _console;
  var backgroundTextStyle = 'padding: 1px 5px;color: #fff;background: ' + consoleColor + ';'
  var textStyle = 'color: ' + consoleColor + ';';

  consoleConfig.map(o => {
    var num = (o.match(/%c/g) || []).length;
    if(/^http(s)?:\/\//.test(o)) {
      console.log('%c     ', 'background: url(' + o + ') no-repeat left center;font-size: 180px;');
      return;
    }
    if (num > 0) {
      var logArguments = [];
      for (var i = 0; i < num; i++) {
        if (i % 2 === 0) {
          logArguments.push(backgroundTextStyle);
        } else {
          logArguments.push(textStyle);
        }
      }
      (_console = console).log.apply(_console, ['%c' + o, textStyle].concat(logArguments));
      return;
    }
    console.log('%c' + o, textStyle);
  });
}(consoleConfig));</script><script type="text/javascript" src="/./js/main.js"></script>

  <script src="//at.alicdn.com/t/font_159214_mvtxvg9me9.js"></script>
</body>
</html>
